# %%
# 初始化
import pandas as pd

import reader
from common import *

(year, month), _ = year_month()

FP_GONGDAN_DANGYUE = (
    f"data/0-工单/家客工单导出_潮州_{year}-{month:02}.zip"
)
FP_ZNZW_SDK = (
    f"data/5-智能组网/{year}{month:02}_智能组网SDK测试清单.xlsx"
)
FP_ZNZW_ZAIXIAN = (
    f"data/5-智能组网/{year}{month:02}_智能组网上线清单.xlsx"
)
check_files(
    FP_GONGDAN_DANGYUE,
    FP_ZNZW_SDK,
    FP_ZNZW_ZAIXIAN,
)

# %%
# 读取工单
usecols = (
    "地市",
    "工单号",
    "宽带帐号",
    "CRM工单号",
    "CRM业务流水号",
    "派单人",
    "操作类型",
    "是否施工标识",
    "产品名称",
    "派单日期",
    "结单时间",
    "工单状态",
    "资源类型",
    "标准地址",
    "产品业务属性",
    "首次第三方复核结果",
    "装维组/用户班",
    "装维人员登录账号",
    "客户类型",
    "五级地址ID",
    "综合网格",
)
df = reader.read_gongdan(FP_GONGDAN_DANGYUE, usecols)

# %%
# 工单筛选
# 操作类型 = 业务开通
# 是否施工标识 = 普通开通施工
# 产品名称 = 智能组网及增值收费服务基础产品
# 工单状态 = 归档
mask = (
    (df["操作类型"] == "业务开通")
    & (df["是否施工标识"] == "普通开通施工")
    & (df["产品名称"] == "智能组网及增值收费服务基础产品")
    & (df["工单状态"] == "归档")
)
df = df[mask]


# %%
# 读取SDK工单数据
df_sdk = pd.read_excel(
    FP_ZNZW_SDK,
    usecols=(
        "组网工单号",
        "套餐名称",
        "工单状态",
        # "SDK测试开始时间",
        # "SDK测试结束时间",
        "累计用时",
        "第一台设备CMEI码",
        "第一台设备纳管状态",
        "安装后评分",
        "装后测试点数",
    ),
    # encoding="gbk",
    # dtype="string",
)
df_sdk.rename(
    columns={
        "安装后评分": "装后分数",
        "装后测试点数": "测试点数",
        "第一台设备CMEI码": "设备CMEI码",
    },
    inplace=True,
)
# df_sdk = df_sdk[
# (df_sdk["工单状态"] == "归档")
# & df_sdk["SDK测试开始时间"].notna()
# & df_sdk["SDK测试结束时间"].notna()
# ]
df_sdk["服务类型"] = "设备安装"
df_sdk.loc[
    df_sdk["套餐名称"].isin(["2020年WiFi上门检测服务", "智能组网基础服务88元"]),
    "服务类型",
] = "纯服务"
# df_sdk["装后分数"] = df_sdk["装后分数"].astype("float")
# df_sdk["测试点数"] = df_sdk["测试点数"].astype("float")
# df_sdk["SDK测试开始时间"] = df_sdk["SDK测试开始时间"].astype("datetime64")
# df_sdk["SDK测试结束时间"] = df_sdk["SDK测试结束时间"].astype("datetime64")
# df_sdk["测试时长(秒)"] = (df_sdk["SDK测试结束时间"] - df_sdk["SDK测试开始时间"]).dt.seconds


# %%
# 测试点数核查
df_sdk["测试点数核查"] = S_NOT_PASSED
df_sdk.loc[df_sdk.测试点数 >= 3, "测试点数核查"] = S_PASSED
# %%
# 装后分数核查
df_sdk["装后分数核查"] = S_NOT_PASSED
df_sdk.loc[df_sdk.装后分数 >= 85, "装后分数核查"] = S_PASSED
# %%
# 时长核查
df_sdk["时长核查"] = S_NOT_PASSED
df_sdk.loc[
    (df_sdk.服务类型 == "纯服务") & (df_sdk["累计用时"] >= 240), "时长核查"
] = S_PASSED
df_sdk.loc[
    (df_sdk.服务类型 == "设备安装") & (df_sdk["累计用时"] >= 360),
    "时长核查",
] = S_PASSED


# %%
# 设备纳管/上线核查
df_sdk["设备纳管/上线核查"] = S_NOT_PASSED
df_sdk.loc[df_sdk.服务类型 == "纯服务", "设备纳管/上线核查"] = S_BYPASSED
df_sdk.loc[
    (df_sdk.服务类型 == "设备安装") & (df_sdk.第一台设备纳管状态 == "正常纳管"),
    "设备纳管/上线核查",
] = S_PASSED

# %%
# 设备CMEI码重复性核查
df_zaixian = pd.read_excel(
    FP_ZNZW_ZAIXIAN,
    usecols=["CMEI"],
    dtype="string",
)
df_sdk["设备CMEI码重复性核查"] = S_BYPASSED
df_cmei_error = df_zaixian.groupby("CMEI").filter(
    lambda x: len(x) > 1
)["CMEI"]
df_sdk.loc[
    (df_sdk.服务类型 == "设备安装")
    & df_sdk.设备CMEI码.isin(df_cmei_error),
    "设备CMEI码重复性核查",
] = S_NOT_PASSED
df_sdk.loc[
    (df_sdk.服务类型 == "设备安装")
    & ~df_sdk.设备CMEI码.isin(df_cmei_error),
    "设备CMEI码重复性核查",
] = S_PASSED

# %%
# 合并
df = df.merge(
    df_sdk[
        [
            "组网工单号",
            "套餐名称",
            "服务类型",
            # "SDK测试开始时间",
            # "SDK测试结束时间",
            # "测试时长(秒)",
            "累计用时",
            "测试点数",
            "设备CMEI码",
            "测试点数核查",
            "装后分数核查",
            "时长核查",
            "设备纳管/上线核查",
            "设备CMEI码重复性核查",
        ]
    ],
    left_on="工单号",
    right_on="组网工单号",
    how="left",
)

# %%
# 稽核结果
check_columns = [
    "测试点数核查",
    "装后分数核查",
    "时长核查",
    "设备纳管/上线核查",
    "设备CMEI码重复性核查",
]
df.loc[
    ~df.工单号.isin(df_sdk.组网工单号),
    check_columns,
] = S_NOT_PASSED

not_passed = (df[check_columns] == S_NOT_PASSED).any(axis=1)
df["地市稽核结果"] = S_PASSED
df.loc[not_passed, "地市稽核结果"] = S_NOT_PASSED

# 是否结算
df["是否结算"] = S_YES
df.loc[not_passed, "是否结算"] = S_NO

# %%
# 智能组网工单地市稽核明细
df.to_excel(
    f"report/5-智能组网/{year}{month:02}_智能组网工单地市稽核明细.xlsx",
    index=False,
)

# %%
# 完成
